﻿-- drop function tesoreria.fxIPM(dfecven date);
-- drop function tesoreria.fxIPM(dfecven timestamp);
create or replace function tesoreria.fxIPM(dfecven2 timestamp)
returns numeric(18, 5) as
$$
declare
	ntramo0 numeric(18, 8);
	ntramo1 numeric(18, 8);
begin
	select nmonipm into ntramo0 from tesoreria.mreajus where cperanio=to_char(dfecven2, 'yyyy') and cnromes=2 and ctiping::int=278;
	select nmonipm into ntramo1 from tesoreria.mreajus where cperanio=to_char(dfecven2, 'yyyy') and cnromes=to_char(dfecven2, 'mm')::int and ctiping::int=278;

	return ntramo1 / (case when ntramo0=0 then 1 else ntramo0 end);
end
$$
language plpgsql volatile cost 100;

create or replace function tesoreria.fxreajus(dfecpro timestamp)
returns table(
	cperanio character(4),
	ctiping character(10),
	cnromes int,
	dfecven timestamp,
	nmontim numeric(18, 5),
	nfacrea numeric(18, 5),
	nfacmor numeric(18, 5),
	nfacipm numeric(18, 5),
	nipmapl int,
	nfacapl numeric(18, 5)
) as
$$
	select a.cperanio
		, a.ctiping
		, a.cnromes
		, a.dfecven
		, a.nmontim
		, a.nfacrea
		, (((date_trunc('hour', dfecpro)::date - date_trunc('hour', a.dfecven)::date)::int * a.nmontim) / 100)::numeric(18, 5) nfacmor
		, tesoreria.fxIPM(a.dfecven) nfacipm
		, a.nipmapl
		, (case when a.nipmapl = 0 then 1 else (a.nfacmor * tesoreria.fxIPM(a.dfecven) * a.nipmapl) end)::numeric(18, 5) nfacapl
	from tesoreria.mreajus a;
$$
language sql volatile cost 100;

-- select * from tesoreria.mreajus
select * from tesoreria.fxIPM(to_date('01/01/2013', 'dd/MM/yyyy'));
-- select * from tesoreria.fxreajus(to_date('01/01/2013', 'dd/MM/yyyy'));

-- select ntramo0=nmonipm from tesoreria.mreajus where cperanio=to_char(to_date('01/01/2012', 'dd/MM/yyyy'), 'yyyy') and cnromes=2 and ctiping::int=278;